/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seari.controller;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.seari.bean.DevLifeBean;
import com.seari.bean.MaterialCaregory;
import com.seari.bean.MaterialCaregoryAttr;
import com.seari.bean.MaterialCaregoryAttrVaule;
import com.seari.bean.Product;
import com.seari.bean.Station;
import com.seari.bean.User;
import com.seari.pojo.DataGridBean;
import com.seari.pojo.MessageBean;
import com.seari.pojo.QueryUser;
import com.seari.service.DevLifeService;
import com.seari.service.MaterialCaregoryAttrService;
import com.seari.service.MaterialCaregoryAttrValueService;
import com.seari.service.MaterialCaregoryService;
import com.seari.service.ProductService;
import com.seari.service.UserService;
import com.seari.utils.ExportExcelUtil;
import com.seari.utils.ParseUtils;
import com.seari.utils.StringHelper;
import com.seari.utils.Tools;

/**
 * 设备分类
 * @author whl
 */
@Controller
@RequestMapping("mcIn")
public class MaterialCaregoryInController
{
    @Autowired
    private MaterialCaregoryService materialCaregoryService;

    @Autowired
    private MaterialCaregoryAttrService materialCaregoryAttrService;

    @Autowired
    private MaterialCaregoryAttrValueService materialCaregoryAttrValueService;

    @Autowired
    private ProductService productService;

    @Autowired
    private DevLifeService devLifeService;

    @Autowired
    private UserService userService;

    @RequestMapping("/getFMcByParams")
    @ResponseBody
    public Map<String,Object> getFMcByParams(HttpServletRequest req)
    {
        Map<String,Object> resultMap = new HashMap<String,Object>();
        DataGridBean<MaterialCaregory> grid = new DataGridBean<MaterialCaregory>();
        String fMcShowName = req.getParameter("fMcShowName");
        String fStatus = req.getParameter("fStatus");
        String fParent = req.getParameter("fParent");
        MaterialCaregory mc = new MaterialCaregory();
        if(StringUtils.isNotBlank(fMcShowName))
        {
            mc.setfMcShowName(fMcShowName);
        }
        if(StringUtils.isNotBlank(fStatus))
        {
            mc.setfStatus(fStatus);
        }
        if(StringUtils.isNotBlank(fParent))
        {
            mc.setfParent(fParent);
        }

        List<MaterialCaregory> materialCaregorys = materialCaregoryService.getFMcByParams(mc);
        grid.setRows(materialCaregorys);

        if(materialCaregorys != null && materialCaregorys.size()>0){
            String fLevel = materialCaregorys.get(0).getfLevel();
            resultMap.put("fLevel",fLevel);
        }else{
            MaterialCaregory materialCategory = new MaterialCaregory();
            materialCategory.setfMcId(fParent);
            materialCategory = materialCaregoryService.getFMcByFMcId(materialCategory);
            if(materialCategory != null){
                resultMap.put("fHasChild",materialCategory.getfHasChild());
            }
        }
        resultMap.put("grid",grid);
        return  resultMap;
    }

    @RequestMapping("/getTitle")
    @ResponseBody
    public Map<String,Object> getTitle(HttpServletRequest req)
    {
        Map<String,Object> resultMap = new HashMap<String,Object>();
        String title = "";
        String fMcId = req.getParameter("fMcId");
        MaterialCaregory mc = new MaterialCaregory();
        if(StringUtils.isNotBlank(fMcId))
        {
            mc.setfMcId(fMcId);
            List<MaterialCaregory> materialCaregorys = materialCaregoryService.getFMcTree(mc);
            if(materialCaregorys != null && materialCaregorys.size()>0){
                MaterialCaregory materialCaregory = materialCaregorys.get(0);
                String fMcShowName1 = materialCaregory.getfMcShowName();
                title = fMcShowName1;
                String fMcShowName2 = "";
                String fMcShowName3 = "";
                if(materialCaregory.getChildren() != null && materialCaregory.getChildren().size()>0){
                    MaterialCaregory materialCaregoryChild =  materialCaregory.getChildren().get(0);
                    fMcShowName2 = materialCaregoryChild.getfMcShowName();
                    title = title + ">" + fMcShowName2;
                    resultMap.put("fMcShowName",fMcShowName2);
                    if(materialCaregoryChild.getChildren() != null && materialCaregoryChild.getChildren().size()>0){
                        fMcShowName3 = materialCaregoryChild.getChildren().get(0).getfMcShowName();
                        title = title + ">" + fMcShowName3;
                        resultMap.put("fMcShowName",fMcShowName3);
                    }
                }else{
                    resultMap.put("fMcShowName",title);
                }
            }else{
                MaterialCaregory materialCaregory = materialCaregoryService.getFMcByFMcId(mc);
                title = materialCaregory.getfMcShowName();
                resultMap.put("fMcShowName",title);
            }
        }

        resultMap.put("title",title);
        return  resultMap;
    }

    @RequestMapping("/getStationList")
    @ResponseBody
    public Map<String,Object> getStationList(HttpServletRequest req)
    {
        Map<String,Object> resultMap = new HashMap<String,Object>();

        List<Station> stationList = materialCaregoryAttrService.getStationByParam(new Station());
        if(stationList != null && stationList.size() > 0){
            resultMap.put("stationList",stationList);
            resultMap.put("stationSize",stationList.size());
            resultMap.put("code","success");
        }else{
            resultMap.put("code","failed");
        }

        return  resultMap;
    }

    @RequestMapping("/getTableCode")
    @ResponseBody
    public Map<String,Object> getTableCode(HttpServletRequest req)
    {
        Map<String,Object> resultMap = new HashMap<String,Object>();
        String fMcId = req.getParameter("fMcId");
        MaterialCaregoryAttr mcAttr = new MaterialCaregoryAttr();
        if(StringUtils.isNotBlank(fMcId))
        {
            mcAttr.setfMcId(fMcId);
            mcAttr.setfCaEnabled("1");
            List<MaterialCaregoryAttr> mcAttrList = materialCaregoryAttrService.getFMcAttrByParams(mcAttr);
            if(mcAttrList != null && mcAttrList.size() > 0){
                resultMap.put("code","success");
                resultMap.put("mcAttrList",mcAttrList);
            }else{
                resultMap.put("code","failed");
            }
            List<Station> stationList = materialCaregoryAttrService.getStationByParam(new Station());
            if(stationList != null && stationList.size() > 0){
                resultMap.put("stationList",stationList);
                resultMap.put("stationSize",stationList.size());
            }

            MaterialCaregory mc = new MaterialCaregory();
            mc.setfMcId(fMcId);
            List<MaterialCaregory> materialCaregorys = materialCaregoryService.getFMcTree(mc);
            if(materialCaregorys != null && materialCaregorys.size()>0){
                MaterialCaregory materialCaregory = materialCaregorys.get(0);
                String fMcShowName2 = "";
                String fMcShowName3 = "";
                if(materialCaregory.getChildren() != null && materialCaregory.getChildren().size()>0){
                    MaterialCaregory materialCaregoryChild =  materialCaregory.getChildren().get(0);
                    fMcShowName2 = materialCaregoryChild.getfMcShowName();
                    resultMap.put("zcTypeId",fMcShowName2);
                    if(materialCaregoryChild.getChildren() != null && materialCaregoryChild.getChildren().size()>0){
                        fMcShowName3 = materialCaregoryChild.getChildren().get(0).getfMcShowName();
                        resultMap.put("zcSubTypeId",fMcShowName3);
                    }
                }
            }

            List<Map<String,String>> lineList = materialCaregoryAttrService.getLineList(null);
            if(lineList != null && lineList.size()>0){
                resultMap.put("lineId",lineList.get(0).get("lineId"));
                resultMap.put("lineList",lineList);
            }

            String uid = ParseUtils.getUidFromToken(req,userService);
            Map<String,String> param = new HashMap<>();
            param.put("USER_ID",uid);
            QueryUser qu = new QueryUser();
            qu.setUid(Integer.parseInt(uid));
             List<User> userByUid = userService.getUserByUid(qu);
            resultMap.put("userId",uid);
            if(userByUid != null){
                resultMap.put("userName",String.valueOf(userByUid.get(0).getUserName()));
            }
            resultMap.put("createTime",Tools.date2Str(new Date()));
        }else{
            resultMap.put("code","failed");
        }
        return  resultMap;
    }

    @RequestMapping("/getStationByStationId")
    @ResponseBody
    public Map<String,Object> getStationByStationId(HttpServletRequest req)
    {
        Map<String,Object> resultMap = new HashMap<String,Object>();
        String stationId = req.getParameter("stationId");
        Station station = new Station();
        if(StringUtils.isNotBlank(stationId))
        {
            station.setStationId(stationId);
            List<Station> stationList = materialCaregoryAttrService.getStationByParam(station);
            if(stationList != null && stationList.size() > 0){
                resultMap.put("stationList",stationList);
                resultMap.put("stationSize",stationList.size());
            }
        }else{
            resultMap.put("code","failed");
        }
        return  resultMap;
    }

    @RequestMapping("/getTableCodeAndValue")
    @ResponseBody
    public Map<String,Object> getTableCodeAndValue(HttpServletRequest req)
    {
        Map<String,Object> resultMap = new HashMap<String,Object>();
        String fMcId = req.getParameter("fMcId");
        String fProductId = req.getParameter("fProductId");
        MaterialCaregoryAttr mcAttr = new MaterialCaregoryAttr();
        if(StringUtils.isNotBlank(fProductId)){
            mcAttr.setfProductId(fProductId);
            if(StringUtils.isBlank(fMcId)){
                Product product = new Product();
                product.setfProductId(fProductId);
                List<Product> productList = productService.getProductById(product);
                if(productList != null && productList.size()>0){
                    fMcId = productList.get(0).getfMcId();
                }
            }
            mcAttr.setfMcId(fMcId);
        }
        List<MaterialCaregoryAttr> mcAttrList = materialCaregoryAttrService.getAttrAndValueByFMcId(mcAttr);
        if(mcAttrList != null && mcAttrList.size() > 0){
            resultMap.put("code","success");
            resultMap.put("mcAttrList",mcAttrList);
        }else{
            resultMap.put("code","failed");
        }

        List<Map<String,String>> lineList = materialCaregoryAttrService.getLineList(null);
        if(lineList != null && lineList.size()>0){
            resultMap.put("lineId",lineList.get(0).get("lineId"));
            resultMap.put("lineList",lineList);
        }

        List<Station> stationList = materialCaregoryAttrService.getStationByParam(new Station());
        if(stationList != null && stationList.size() > 0){
            resultMap.put("stationList",stationList);
        }
        return  resultMap;
    }

    @RequestMapping("saveAttrValue")
    @ResponseBody
    public MessageBean saveAttrValue(HttpServletRequest request) {
        String userId = ParseUtils.getUidFromToken(request,userService);
        MessageBean mb = new MessageBean();
        String fMcId = request.getParameter("fMcId");

        if(StringUtils.isBlank(fMcId)) {
            //表单验证
            mb.setCode(StringHelper.MESSAGE_TYPE_ERROR);
            mb.setMessage("表单信息不完整");
            return mb;
        }

        //获取字段的主键值,封装成Map
        MaterialCaregoryAttr mc = new MaterialCaregoryAttr();
        mc.setfMcId(fMcId);
        List<MaterialCaregoryAttr> mcAttrList = materialCaregoryAttrService.getFMcAttrByParams(mc);
        Map<String,String> mcAttrMap = new HashMap<>();
        for (MaterialCaregoryAttr mcAttr : mcAttrList) {
            mcAttrMap.put(mcAttr.getfCaCode(),mcAttr.getfCaId());
        }

        //获取所以字段和字段值，封装成Product和List<MaterialCaregoryAttrVaule>
        Map<String, String[]> properties = request.getParameterMap();//把请求参数封装到Map<String, String[]>中
        Map<String, String> returnMap = new HashMap<>();
        String name = "";
        String value = "";
        String fProductId = Tools.get32UUID();
        Product product = new Product();
        product.setfProductId(fProductId);
        product.setfMcId(fMcId);
        List<MaterialCaregoryAttrVaule> mcAttrValueList = new ArrayList<>();
        MaterialCaregoryAttrVaule mcAttrValue = null;

        DevLifeBean devLifeBean = new DevLifeBean();
        devLifeBean.setProductId(fProductId);
//        0：录入
//        1：编辑
//        2：删除
//        3：删除恢复
//        4：计划维护
//        5：故障检修
//        6：报警
//        7：报警恢复
        devLifeBean.setLifeStatus("0");
        for (Map.Entry<String, String[]> entry : properties.entrySet()) {
            name = entry.getKey();
            if(!"fMcId".equals(name)){
                mcAttrValue = new MaterialCaregoryAttrVaule();
                String[] values = entry.getValue();
                if (null == values) {
                    value = "";
                } else if (values.length > 1) {
                    for (int i = 0; i < values.length; i++) { //用于请求参数中有多个相同名称
                        value = values[i] + ",";
                    }
                    value = value.substring(0, value.length() - 1);
                } else {
                    value = values[0];//用于请求参数中请求参数名唯一
                }
                returnMap.put(name, value);
                if("ZC_CODE".equals(name)){
                    product.setZcCode(value);//资材编码(实际编码)
                }else if("ZC_NAME".equals(name)){
                    product.setfProductShowName(value);
                    product.setfProductName(value);
                }else if("STATION_ID".equals(name)){
                    product.setStationId(value);
                }
                mcAttrValue.setfProductId(fProductId);
                mcAttrValue.setfCaId(mcAttrMap.get(name));
                mcAttrValue.setfMcaValue(value);
                mcAttrValueList.add(mcAttrValue);
            }
        }

        //设置站点ID
        for(MaterialCaregoryAttrVaule attrValue : mcAttrValueList){
            attrValue.setStationId(product.getStationId());
        }

        devLifeBean.setRemark(product.getfProductShowName());
        devLifeBean.setCureateUserId(userId);

        //保存到数据库
        List<Product> productList = new ArrayList<>();
        productList.add(product);
        int resultCountProduct = productService.addProductBatch(productList);
        int resultCountMcAttrValue = materialCaregoryAttrValueService.addMcAttrValueBatch(mcAttrValueList);

        if(resultCountProduct > 0 && resultCountMcAttrValue > 0) {
            mb.setCode(StringHelper.MESSAGE_TYPE_SUCCESS);
            mb.setMessage("保存资材属性值成功");
            devLifeService.saveDevLifeInfo(devLifeBean);//保存到设备生命周期表中
        } else {
            mb.setCode(StringHelper.MESSAGE_TYPE_ERROR);
            mb.setMessage("保存资材属性值失败");
        }

        return  mb;
    }

    /**
     * 下载资材导入模板。
     *
     * @param response
     * @throws Exception
     * @see
     */
    @RequestMapping(value = "/downloadCateTemplate")
    public void downloadCateTemplate(HttpServletRequest request,HttpServletResponse response)
            throws Exception
    {
        // 获取参数
        String fMcId = request.getParameter("fMcId");
        String fMcShowName = request.getParameter("fMcShowName");
        MaterialCaregoryAttr mcAttr = new MaterialCaregoryAttr();
        mcAttr.setfMcId(fMcId);
        mcAttr.setfCaEnabled("1");
        // 获得资材分类属性数据
        List<MaterialCaregoryAttr> data = materialCaregoryAttrService.getFMcAttrByParams(mcAttr);
        // 返回文件的文件名
        String fileName = fMcShowName + ".xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        // 设置返回消息头
        response.reset();
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        response.setContentType(
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        // 获取返回的流
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        // 新建excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 装载数据
        setDatas(workbook, data, fMcShowName);
        // 回写文件
        ExportExcelUtil.writeFile(response, out, workbook);
    }

    /**
     * 把数据库查询出来的数据填入excel中。
     *
     * @param workbook
     * @param data
     * @see
     */
    public void setDatas(HSSFWorkbook workbook, List<MaterialCaregoryAttr> data,String fMcShowName)
    {
        int length = data.size();
        if (length != 0)
        {
            // 添加sheet
            HSSFSheet sheet1 = workbook.createSheet("数据");
            HSSFSheet sheet2 = workbook.createSheet("字段名");
            HSSFSheet sheet3 = workbook.createSheet("操作说明");
            HSSFRow sheet3row0 = sheet3.createRow(0);
            HSSFRow sheet3row1 = sheet3.createRow(1);
            HSSFRow sheet3row2 = sheet3.createRow(2);
            HSSFRow sheet3row3 = sheet3.createRow(3);
            Cell sheet3row0cell0 = sheet3row0.createCell(0);
            sheet3row0cell0.setCellValue("1、请在【数据】页填写要导入的数据，【字段】页为系统参数配置，请勿操作。");
            Cell sheet3row1cell0 = sheet3row1.createCell(0);
            sheet3row1cell0.setCellValue("2、请将所有将要填写数据的列选中，将单元格格式改为文本。");
            Cell sheet3row2cell0 = sheet3row2.createCell(0);
            sheet3row2cell0.setCellValue("3、日期格式请写成YYYY-MM-DD,例2019-01-01。");
            Cell sheet3row3cell0 = sheet3row3.createCell(0);
            sheet3row3cell0.setCellValue("4、如有其他疑问，请联系管理员。");

            // 在sheet1中添加第一行，将类型名称填入
            HSSFRow row = sheet1.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(fMcShowName);
            // 合并单元格
            sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, length));
            // 遍历，将字段名称填入sheet1第二行,将字段编码放入sheet2第一行
            HSSFRow sheet2row0 = sheet2.createRow(0);
            HSSFRow sheet2row1 = sheet2.createRow(1);
            HSSFRow sheet2row2 = sheet2.createRow(2);
            HSSFRow row1 = sheet1.createRow(1);
            // 将分类ID存入sheet2第一行第一列
            Cell cellTemp0 = sheet2row0.createCell(0);
            cellTemp0.setCellValue(data.get(0).getfMcId());
            setCellStyleString(cellTemp0, workbook);
            for (int i = 0; i < length; i++ )
            {
                // 将字段名称填入sheet1第二行
                Cell cellTemp1 = row1.createCell(i);
                cellTemp1.setCellValue(data.get(i).getfCaShowName());
                // 将字段编码放入sheet2第二行
                Cell cellTemp2 = sheet2row1.createCell(i);
                cellTemp2.setCellValue(data.get(i).getfCaCode());
                // 将字段编码属性编号放入sheet2第三行
                Cell cellTemp3 = sheet2row2.createCell(i);
                cellTemp3.setCellValue(data.get(i).getfCaId());
            }

        }

    }

    @SuppressWarnings("resource")
    @RequestMapping(value = "/importProduct")
    @ResponseBody
    public Map<String,String> importProduct(@RequestParam(value = "file") MultipartFile file,
                                HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        Map<String, String> result = new HashMap<String, String>();
        List<Product> productList = new ArrayList<Product>();
        List<DevLifeBean> devLifeBeanList = new ArrayList<DevLifeBean>();
        List<MaterialCaregoryAttrVaule> mcAttrValueList = new ArrayList<MaterialCaregoryAttrVaule>();

        String userId = ParseUtils.getUidFromToken(request,userService);

        try {
            InputStream in = file.getInputStream();
            HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(in));

            HSSFSheet sheet1 = wb.getSheetAt(1);// 获取字段名页
            HSSFRow row0 = sheet1.getRow(0);// 获取保存分类ID的行
            HSSFRow row1 = sheet1.getRow(1);// 获取保存分类编码的行
            HSSFRow row2 = sheet1.getRow(2);// 获取保存分类属性编码的行
            Cell cell = row0.getCell(0);// 获取保存资材分类ID的cell
            String f_mc_id = cell.getStringCellValue();// 资材分类ID

            HSSFSheet sheet = wb.getSheetAt(0);// 获取数据页
            int totalRows = sheet.getLastRowNum();// 最后一行数据的行号
            int rownum = 2;// 数据开始的行号
            if (totalRows >= rownum) {
                for (int i = rownum; i <= totalRows; i++ ) {
                    HSSFRow row = sheet.getRow(i);
                    Map<String,Object> data = assembleDatas(row, row1, row2, f_mc_id, i,userId);

                    productList.add((Product)data.get("product"));
                    devLifeBeanList.add((DevLifeBean)data.get("devLifeBean"));
                    mcAttrValueList.addAll((List<MaterialCaregoryAttrVaule>)data.get("mcAttrValueList"));
                }
                int resultCountProduct = productService.addProductBatch(productList);
                int resultCountMcAttrValue = materialCaregoryAttrValueService.addMcAttrValueBatch(mcAttrValueList);
                int resultCountDevLife = devLifeService.batchSaveDevLife(devLifeBeanList);
                if(resultCountProduct>0 && resultCountMcAttrValue>0 && resultCountDevLife>0){
                    result.put("code", "success");
                    result.put("msg", "导入成功");
                }else{
                    result.put("code", "failed");
                    result.put("msg", "导入失败");
                }
            }else{
                result.put("code", "success");
                result.put("msg", "导入数据0条，原因：可能文件内容为空。。。");
            }

            System.out.println("导入文件数据总量：" + (totalRows - 1));

        } catch (IOException e) {
            e.printStackTrace();
        }

        return result;
    }

    /**
     * Description: 组装导入的数据。
     *
     * @param row
     *            数据行
     * @param row1
     *            字段名行
     * @param f_mc_id
     *            资材分类ID
     * @see
     */
    @SuppressWarnings("unchecked")
    private Map<String,Object> assembleDatas(HSSFRow row, HSSFRow row1, HSSFRow row2,
                                             String f_mc_id, int index, String userId)
    {
        Map<String,Object> map = new HashMap<>();

        int totalColCells = row1.getLastCellNum();//总字段数

        String fProductId = Tools.get32UUID();
        Product product = new Product();
        product.setfMcId(f_mc_id);
        product.setfProductId(fProductId);

        List<MaterialCaregoryAttrVaule> mcAttrValueList = new ArrayList<MaterialCaregoryAttrVaule>();
        MaterialCaregoryAttrVaule materialCaregoryAttrVaule = null;
        DevLifeBean devLifeBean = new DevLifeBean();
        devLifeBean.setProductId(fProductId);
        devLifeBean.setLifeStatus("0");
        devLifeBean.setCureateUserId(userId);
        for (int i = 0; i < totalColCells; i++ ) {
            materialCaregoryAttrVaule = new MaterialCaregoryAttrVaule();
            materialCaregoryAttrVaule.setfProductId(fProductId);
            materialCaregoryAttrVaule.setfMcaEnabled("1");

            String val = "";
            try {
                HSSFCell cell = row.getCell(i);
                DecimalFormat df = new DecimalFormat("#");
                switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_STRING:
                        val = cell.getRichStringCellValue().getString().trim();
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if(HSSFDateUtil.isCellDateFormatted(cell)){//like12 add,20180622,支持日期格式
                            Date d = cell.getDateCellValue();
                            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//HH:mm:ss
                            val = dateFormat.format(d);
                        } else {//数字
                            val = df.format(cell.getNumericCellValue()).toString();
                        }
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        val = String.valueOf(cell.getBooleanCellValue()).trim();
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        val = cell.getCellFormula();
                        break;
                    default:
                        val = "";
                }
            } catch (NullPointerException e) {
                System.out.println("行" + index + "列" + i + "没有填写数据");
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("行" + index + "列" + i + "读取失败，原因未知");
            }
            String key = row1.getCell(i).toString();
            String keyCodeValue = row2.getCell(i).toString();
            if("ZC_CODE".equals(key)){
                product.setZcCode(val);//资材编码(实际编码)
            }else if("ZC_NAME".equals(key)){
                product.setfProductName(val);
                product.setfProductShowName(val);
                devLifeBean.setRemark(val);
            }else if("STATION_ID".equals(key)){
                product.setStationId(val);
                materialCaregoryAttrVaule.setStationId(val);
            }else if("INSTALL_DATE".equals(key)){
                DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");//HH:mm:ss
                if(StringUtils.isNotBlank(val)){
                    val = dateFormat.format(Tools.str2Date(val));
                }
            }
            materialCaregoryAttrVaule.setfCaId(keyCodeValue);
            materialCaregoryAttrVaule.setfMcaValue(val);
            if(StringUtils.isNotBlank(val)){
                mcAttrValueList.add(materialCaregoryAttrVaule);
            }
        }

        map.put("product",product);
        map.put("devLifeBean",devLifeBean);
        for(MaterialCaregoryAttrVaule attrValue : mcAttrValueList){
            attrValue.setStationId(product.getStationId());
        }
        map.put("mcAttrValueList",mcAttrValueList);
        return map;
    }

    private void setCellStyleString(Cell cell, HSSFWorkbook wb)
    {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFDataFormat format = wb.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@"));
        cell.setCellStyle(cellStyle);
    }

    /*
     * 校验：购置时间，折旧年限，质保开始时间，质保年限
     */
    private String validate(String validMsg, String key, String value)
    {
        String[] cols = {"REVERB_TIME", "DEPRECIATION", "WARRANTY_STARTTIME", "WARRANTY_YEAR"};
        if (key.equals(cols[0]) || key.equals(cols[2]))
        {
            String eL = "[0-9]{4}-[0-9]{2}-[0-9]{2}";
            Pattern p = Pattern.compile(eL);
            Matcher m = p.matcher(value);
            boolean dateFlag = m.matches();
            if (!dateFlag)
            {
                if (key.equals("REVERB_TIME"))
                {
                    validMsg += "【购置时间日期格式错误】";
                }
                else
                {
                    validMsg += "【质保开始时间日期格式错误】";
                }
            }
        }

        if (key.equals(cols[1]) || key.equals(cols[3]))
        {
            String eL = "^\\+?[1-9][0-9]*$";
            Pattern p = Pattern.compile(eL);
            Matcher m = p.matcher(value);
            boolean dateFlag = m.matches();
            if (!dateFlag)
            {
                if (key.equals("DEPRECIATION"))
                {
                    validMsg += "【折旧年限不是正整数】";
                }
                else
                {
                    validMsg += "【质保年限不是正整数】";
                }
            }
        }
        return validMsg;
    }
}
